package hit.dao.database;

import hit.core.HITBase;
import hit.core.ProductGroup;
import hit.core.Size;
import hit.core.StorageUnit;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Data Access Object that accesses {@link StorageUnit}s in the database
 */
public class DBProductGroupDAO extends DBBase {

	
	/**
	 * @param databaseDAO a pointer to the DatabaseDAO that created this
	 * DBStorageUnitDAO
	 * */
	public DBProductGroupDAO(DatabaseDAO databaseDAO){
		super(databaseDAO);
	}
	
	@Override
	protected PreparedStatement InitGetQuery() throws SQLException {
		String sql = "SELECT c.container_id, c.name, c.parent_id, s.name, p.size_value " +
				"FROM container AS c " +
				"INNER JOIN product_group AS p ON  c.container_id = p.container_id " +
				"INNER JOIN size_type AS s ON p.size_type_id = s.size_type_id";
		return databaseDAO.GetConnection().prepareStatement(sql);
	}

	@Override
	protected List<HITBase> ProcessGetRS(ResultSet rs) throws SQLException {
		List<HITBase> results = new ArrayList<HITBase>();
		
		while (rs.next()) {
			ProductGroup group = new ProductGroup(rs.getString(2));
			group.setId(rs.getInt(1));
			group.SetParentID(rs.getInt(3));
			group.SetThreeMonth(Size.SizeFromString(rs.getString(4), rs.getDouble(5)));
			
			results.add(group);
		}
		
		return results;
	}

	@Override
	protected List<PreparedStatement> InitCreateStatement(HITBase object) throws SQLException {
		String sql1 = "INSERT INTO container (name, parent_id) VALUES (?, ?)";
		String sql3 = "SELECT last_insert_rowid()";
		String sql2 = "INSERT INTO product_group (size_value, size_type_id, container_id) " +
				"VALUES (?, ?, last_insert_rowid())";
		
		ProductGroup group = (ProductGroup) object;
		
		List<PreparedStatement> stmts = new ArrayList<PreparedStatement>();
		
		PreparedStatement stmt1 = databaseDAO.GetConnection().prepareStatement(sql1);
		stmt1.setString(1, group.GetName());
		stmt1.setInt(2, group.GetParentID());
		stmts.add(stmt1);
		
		PreparedStatement stmt3 = databaseDAO.GetConnection().prepareStatement(sql3);
		stmts.add(stmt3);
		
		PreparedStatement stmt2 = databaseDAO.GetConnection().prepareStatement(sql2);
		stmt2.setDouble(1, group.GetThreeMonth().GetAmount());
		stmt2.setInt(2, group.GetThreeMonth().GetType().GetDatabaseID());
		stmts.add(stmt2);
		
		return stmts;
	}

	@Override
	protected List<PreparedStatement> InitRemoveStatement(HITBase object) throws SQLException {
		String sql1 = "DELETE FROM container WHERE container_id = ?";
		String sql2 = "DELETE FROM product_group WHERE container_id = ?";
		
		ProductGroup group = (ProductGroup) object;
		
		List<PreparedStatement> stmts = new ArrayList<PreparedStatement>();
		
		PreparedStatement stmt1 = databaseDAO.GetConnection().prepareStatement(sql1);
		stmt1.setInt(1, group.getId());
		stmts.add(stmt1);
		
		PreparedStatement stmt2 = databaseDAO.GetConnection().prepareStatement(sql2);
		stmt2.setInt(1, group.getId());
		stmts.add(stmt2);
		
		return stmts;
	}

	@Override
	protected List<PreparedStatement> InitUpdateQuery(HITBase object)
			throws SQLException {
		List<PreparedStatement> stmts = new ArrayList<PreparedStatement>();
		ProductGroup group = (ProductGroup) object;
		
		String sql1 = "UPDATE container SET name = ? WHERE container_id = ?";
		String sql2 = "UPDATE product_group SET size_value = ?, size_type_id = ? " +
				"WHERE container_id = ?";
		
		PreparedStatement stmt1 = databaseDAO.GetConnection().prepareStatement(sql1);
		stmt1.setString(1, group.GetName());
		stmt1.setInt(2, group.getId());
		stmts.add(stmt1);
		
		PreparedStatement stmt2 = databaseDAO.GetConnection().prepareStatement(sql2);
		stmt2.setDouble(1, group.GetThreeMonth().GetAmount());
		stmt2.setInt(2, group.GetThreeMonth().GetType().GetDatabaseID());
		stmt2.setInt(3, group.getId());
		stmts.add(stmt2);
		
		return stmts;
	}
}
